const connection = require('../app/database')

class MomentService {
    async created(content, userId) {
        const statement = `INSERT INTO moment (content, user_id) VALUES (?,?);`;
        const [result] = await connection.execute(statement, [content, userId])
        return result
    }
    async queryList(offset, size) {
        const statement = `SELECT 
        m.id id, m.content content, m.create_At creata_At, m.update_At update_At,
        JSON_OBJECT('id', u.id, 'name', u.name, 'creata_At', u.creata_At,'update_At',u.update_At,'avatarURL',u.avatar_url) user,
        (SELECT COUNT(*) FROM comment WHERE comment.moment_id = m.id) commentCount,
        (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount
        FROM moment m
        LEFT JOIN user u ON u.id = m.user_id
        LIMIT ? OFFSET ?;`
        const [result] = await connection.execute(statement, [size, offset])
        return result
    }
    async queryParams(momentId) {
        const statement = `SELECT 
                m.id id, m.content content, m.create_At create_At, m.update_At update_At,
                JSON_OBJECT('id', u.id, 'name', u.name,'avatarURL',u.avatar_url, 'create_At', u.creata_At,'update_At',u.update_At) user,
                (
                    SELECT 
                        JSON_ARRAYAGG(JSON_OBJECT(
                            'id',c.id,'content',c.content,'comment_id',comment_id,'user',
                            JSON_OBJECT(
                                'id',cu.id, 'name',cu.name,'avatarURL',cu.avatar_url)
                        )) commments
                    FROM comment c
                    LEFT JOIN user cu ON c.user_id = cu.id
                    WHERE c.comment_id = m.id
                ) comments,
                (
                    SELECT 
                        JSON_ARRAYAGG(JSON_OBJECT(
                            'id',l.id,'name',l.name
                        )) 
                ) labels
            FROM moment m
            LEFT JOIN user u ON u.id = m.user_id
            LEFT JOIN moment_label ml ON ml.moment_id = m.id
            LEFT JOIN label l ON ml.label_id = l.id
            WHERE m.id = ?
            GROUP BY m.id;
    `
        const [result] = await connection.execute(statement, [momentId])
        return result
    }
    async updated(momentId, content) {
        const statement = `UPDATE moment SET content = ? WHERE id = ?`
        const [result] = await connection.execute(statement, [content, momentId])
        return result
    }
    async remove(id) {
        const statement = 'DELETE from moment WHERE id = ?;'
        const [result] = await connection.execute(statement, [id])
        return result
    }
    async hasLabel(momentId, labelId) {
        const statement = 'SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?;'
        const [result] = await connection.execute(statement, [momentId, labelId])
        return !!result.length
    }
    async addLabel(momentId, labelId) {
        const statement = 'INSERT INTO `moment_label`(moment_id,label_id) VALUES(?,?);'
        const [result] = await connection.execute(statement, [momentId, labelId])
        return result
    }

}
module.exports = new MomentService()